clear all
cap close
set more off

cd ".\Data_Program"

* This file match counties with their neighbors' fips code and neighbors' fintech penetration

* I. Distance data
use Econdata\sf12010countydistancemiles

gen st1=floor(county1/1000)
gen st2=floor(county2/1000)
drop if inlist(st1,2,15)|st1>56
drop if inlist(st2,2,15)|st2>56

keep if mi<=200  
gen wt = 1/(1+mi)

expand 44   //2007-2017

sort county1 county2
by county1 county2: gen quarter=q(2007q1)+_n-1
format quarter %tq


* II. Merge with county-level finshare 
rename county2 county
merge m:1 county quarter using county
keep if _merge==3 

keep quarter county1 county wt mi finshare finshare_refi


* county finshare_ad
merge m:1 county quarter using Proprietary\mintel
drop if _merge==2 
drop _merge
replace finad=0 if finad==.
replace nonfinad=0 if nonfinad==.
replace ad=0 if ad==.

    * house price growth
merge m:1 county quarter using Proprietary\hpi_county_quarter_exp
drop if _merge==2 
drop _merge qhpi
replace hpgr=hpgr4

    * population growth
gen year=yofd(dofq(quarter))
merge m:1 year county using Econdata\pop_county
drop if _merge==2  
drop _merge fracm frach pop


    * employment growth
merge m:1 year county using Econdata\emp_county_year
drop if _merge==2  
drop _merge 

    * CCP subprime share
merge m:1 quarter county using Proprietary\subprime_county_quarter
drop if _merge==2 
drop _merge 

     * education level in 2010
merge m:1 county using Econdata\edu
drop if _merge==2  
drop _merge hsabove


* III weighted average
sort quarter county1
gen weight = wt

foreach x of varlist finshare finshare_refi hpgr popgr fracy fracb empgr subprime finad nonfinad ad babove{
by quarter county1: egen `x'2=sum(weight*`x')
}

by quarter county1: keep if _n==1

keep county1 quarter *2 

rename county1 county

xtset county quarter
save distance_weighted,replace